package com.gitee.ricesugar.mall.model.DAO;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.gitee.ricesugar.mall.model.POJO.DO.CartItemDO;
import com.gitee.ricesugar.mall.model.POJO.DO.OrderDO;
import com.gitee.ricesugar.mall.model.POJO.DTO.CartItemDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.UUID;

public class OrderDAO {
    Connection connection;

    public OrderDAO() throws SQLException, ClassNotFoundException {
        connection = ConnectionPool.getInstance();
    }


    public void buyList(ArrayList<CartItemDO> list) throws SQLException, JsonProcessingException {
        while (true) {
            int orderId = UUID.randomUUID().toString().hashCode();
            orderId = Math.abs(orderId);
            PreparedStatement pStatement = connection.prepareStatement("SELECT * FROM `order` WHERE order_id = ?");
            pStatement.setInt(1, orderId);
            ResultSet resultSet = pStatement.executeQuery();

            if (!resultSet.next()) {

                connection.setAutoCommit(false);

                double totalPrice = 0;

                for (CartItemDO item : list) {
                    PreparedStatement select = connection.prepareStatement("SELECT price FROM product WHERE product_id = ?");
                    select.setInt(1, item.getProductId());
                    ResultSet priceResult = select.executeQuery();
                    priceResult.next();
                    double price = priceResult.getDouble("price");
                    double total = price * item.getAmount();

                    PreparedStatement insert = connection.prepareStatement("INSERT INTO orderitem (`order_id`, `product_id`, `amount`, `total_price`) VALUES (?, ?, ?, ?)");
                    insert.setInt(1, orderId);
                    insert.setInt(2, item.getProductId());
                    insert.setInt(3, item.getAmount());
                    insert.setDouble(4, total);
                    insert.executeUpdate();

                    totalPrice += total;
                }

                PreparedStatement select = connection.prepareStatement("SELECT uid FROM user WHERE username = ?");
                select.setString(1, list.get(0).getUsername());
                ResultSet result = select.executeQuery();
                result.next();
                int userId = result.getInt("uid");

                PreparedStatement insert = connection.prepareStatement("INSERT INTO `order` (`order_id`, `user_id`, `total_price`) VALUES (?, ?, ?)");
                insert.setInt(1, orderId);
                insert.setInt(2, userId);
                insert.setDouble(3, totalPrice);
                insert.executeUpdate();

                connection.commit();
                break;
            } else {
                System.out.println(resultSet.getString("id") + " id already exists");
            }
        }
    }

    public void buyItem(CartItemDO item) throws SQLException {
        while (true) {
            int orderId = UUID.randomUUID().toString().hashCode();
            orderId = Math.abs(orderId);
            PreparedStatement pStatement = connection.prepareStatement("SELECT * FROM `order` WHERE order_id = ?");
            pStatement.setInt(1, orderId);
            ResultSet resultSet = pStatement.executeQuery();

            if (!resultSet.next()) {
                PreparedStatement statement = connection.prepareStatement("CALL buyItem(?, ?, ?, ?)");
                statement.setInt(1, orderId);
                statement.setInt(2, item.getProductId());
                statement.setString(3, item.getUsername());
                statement.setInt(4, item.getAmount());
                statement.execute();
                break;
            }
        }
    }

    public ArrayList<OrderDO> listOrder(String username) throws SQLException {
        // 查询用户uid
        PreparedStatement selectUid = connection.prepareStatement("SELECT uid FROM user WHERE username = ?");
        selectUid.setString(1, username);
        ResultSet uidResultSet = selectUid.executeQuery();
        uidResultSet.next();
        int uid = uidResultSet.getInt("uid");

        //查询订单列表
        PreparedStatement selectOrder = connection.prepareStatement("SELECT * FROM `order` WHERE user_id = ?");
        selectOrder.setInt(1, uid);
        ResultSet orderResultSet = selectOrder.executeQuery();

        //遍历订单，查找订单中商品信息
        ArrayList <OrderDO> orderList = new ArrayList<>();
        while (orderResultSet.next()) {

            int order_id = orderResultSet.getInt("order_id");
            Date date = orderResultSet.getDate("date");

            ArrayList<CartItemDTO> items = new ArrayList<>();

            PreparedStatement selectOrderItems = connection.prepareStatement("SELECT * FROM orderitem WHERE order_id = ?");
            selectOrderItems.setInt(1, order_id);
            ResultSet orderItemResultSet = selectOrderItems.executeQuery();

            //遍历商品信息存入数组
            while (orderItemResultSet.next()){
                int product_id = orderItemResultSet.getInt("product_id");
                int amount = orderItemResultSet.getInt("amount");

                PreparedStatement selectItem = connection.prepareStatement("SELECT * FROM product WHERE product_id = ?");
                selectItem.setInt(1, product_id);
                ResultSet itemResultSet = selectItem.executeQuery();
                itemResultSet.next();

                String name = itemResultSet.getString("name");
                double price = itemResultSet.getDouble("price");
                String img = itemResultSet.getString("img");

                items.add(new CartItemDTO(product_id, name, null, amount, price, img));
            }

            //将订单信息存入数组
            orderList.add(new OrderDO(order_id, date, items));
        }

        return orderList;
    }
}
